{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b699e295",
   "metadata": {},
   "source": [
    "# Mean encoding - expanding window\n",
    "\n",
    "[Feature Engineering for Time Series Forecasting](https://www.trainindata.com/p/feature-engineering-for-forecasting)\n",
    "\n",
    "In this notebook, we will encode static features with mean encoding by using expanding windows. This implementation avoids look-ahead bias.\n",
    "\n",
    "We will use the online retail dataset, which we prepared in the notebook `02-create-online-retail-II-datasets.ipynb` located in the `01-Create-Datasets` folder."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "49b2f0bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a174f3b",
   "metadata": {},
   "source": [
    "## Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "67a2af74",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>quantity</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>week</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2009-12-06</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>143</td>\n",
       "      <td>439.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-12-13</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>10</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-12-20</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-12-27</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            country  quantity  revenue\n",
       "week                                  \n",
       "2009-12-06  Belgium       143    439.1\n",
       "2009-12-13  Belgium        10      8.5\n",
       "2009-12-20  Belgium         0      0.0\n",
       "2009-12-27  Belgium         0      0.0\n",
       "2010-01-03  Belgium         0      0.0"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"../Datasets/online_retail_dataset_countries.csv\",\n",
    "                parse_dates=[\"week\"],\n",
    "                index_col=\"week\",\n",
    "                )\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50846272",
   "metadata": {},
   "source": [
    "## Split into train and test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1f4c0763",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Split data before an after June 2011\n",
    "\n",
    "X_train = df[df.index <= pd.to_datetime('2011-06-30')]\n",
    "\n",
    "# We need the past data for the expanding window.\n",
    "X_test = df.copy()\n",
    "\n",
    "# the target variable\n",
    "y_train = X_train[\"revenue\"]\n",
    "y_test = X_test[\"revenue\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e1418b42",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Timestamp('2009-12-06 00:00:00'), Timestamp('2011-06-26 00:00:00'))"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sanity check\n",
    "\n",
    "X_train.index.min(), X_train.index.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1faf10f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Timestamp('2009-12-06 00:00:00'), Timestamp('2011-12-11 00:00:00'))"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sanity check\n",
    "\n",
    "X_test.index.min(), X_test.index.max()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5de7aa0",
   "metadata": {},
   "source": [
    "## Encode countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "931e9ef9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>week</th>\n",
       "      <th>country_enc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>2009-12-06</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>2009-12-13</td>\n",
       "      <td>439.100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>2009-12-20</td>\n",
       "      <td>223.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>2009-12-27</td>\n",
       "      <td>149.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>2010-01-03</td>\n",
       "      <td>111.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>487</th>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2011-05-29</td>\n",
       "      <td>129923.850701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488</th>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2011-06-05</td>\n",
       "      <td>129810.417487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>489</th>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2011-06-12</td>\n",
       "      <td>129208.338025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>490</th>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2011-06-19</td>\n",
       "      <td>129708.159425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>491</th>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2011-06-26</td>\n",
       "      <td>129598.153506</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>492 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            country       week    country_enc\n",
       "0           Belgium 2009-12-06            NaN\n",
       "1           Belgium 2009-12-13     439.100000\n",
       "2           Belgium 2009-12-20     223.800000\n",
       "3           Belgium 2009-12-27     149.200000\n",
       "4           Belgium 2010-01-03     111.900000\n",
       "..              ...        ...            ...\n",
       "487  United Kingdom 2011-05-29  129923.850701\n",
       "488  United Kingdom 2011-06-05  129810.417487\n",
       "489  United Kingdom 2011-06-12  129208.338025\n",
       "490  United Kingdom 2011-06-19  129708.159425\n",
       "491  United Kingdom 2011-06-26  129598.153506\n",
       "\n",
       "[492 rows x 3 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# train set first\n",
    "\n",
    "train_enc = (\n",
    "    X_train\n",
    "    .groupby(['country'])['revenue']\n",
    "    .expanding()\n",
    "    .mean()\n",
    "    .shift()\n",
    ").reset_index()\n",
    "\n",
    "train_enc.rename(columns = {\"revenue\": \"country_enc\"}, inplace = True)\n",
    "\n",
    "train_enc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6d3d07a7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week</th>\n",
       "      <th>country</th>\n",
       "      <th>quantity</th>\n",
       "      <th>revenue</th>\n",
       "      <th>country_enc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-12-06</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>143</td>\n",
       "      <td>439.10</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009-12-13</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>10</td>\n",
       "      <td>8.50</td>\n",
       "      <td>439.100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009-12-20</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>223.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009-12-27</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>149.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010-01-03</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>111.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>487</th>\n",
       "      <td>2011-05-29</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>67666</td>\n",
       "      <td>121076.06</td>\n",
       "      <td>129923.850701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488</th>\n",
       "      <td>2011-06-05</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>44422</td>\n",
       "      <td>82246.14</td>\n",
       "      <td>129810.417487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>489</th>\n",
       "      <td>2011-06-12</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>77850</td>\n",
       "      <td>169194.05</td>\n",
       "      <td>129208.338025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>490</th>\n",
       "      <td>2011-06-19</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>68207</td>\n",
       "      <td>120797.68</td>\n",
       "      <td>129708.159425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>491</th>\n",
       "      <td>2011-06-26</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>57102</td>\n",
       "      <td>90786.39</td>\n",
       "      <td>129598.153506</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>492 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          week         country  quantity    revenue    country_enc\n",
       "0   2009-12-06         Belgium       143     439.10            NaN\n",
       "1   2009-12-13         Belgium        10       8.50     439.100000\n",
       "2   2009-12-20         Belgium         0       0.00     223.800000\n",
       "3   2009-12-27         Belgium         0       0.00     149.200000\n",
       "4   2010-01-03         Belgium         0       0.00     111.900000\n",
       "..         ...             ...       ...        ...            ...\n",
       "487 2011-05-29  United Kingdom     67666  121076.06  129923.850701\n",
       "488 2011-06-05  United Kingdom     44422   82246.14  129810.417487\n",
       "489 2011-06-12  United Kingdom     77850  169194.05  129208.338025\n",
       "490 2011-06-19  United Kingdom     68207  120797.68  129708.159425\n",
       "491 2011-06-26  United Kingdom     57102   90786.39  129598.153506\n",
       "\n",
       "[492 rows x 5 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add encoded variable to original train set\n",
    "\n",
    "X_train_enc = X_train.reset_index().merge(train_enc)\n",
    "\n",
    "X_train_enc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5f6bf153",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>quantity</th>\n",
       "      <th>revenue</th>\n",
       "      <th>country_enc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>week</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2009-12-06</th>\n",
       "      <td>143</td>\n",
       "      <td>439.1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-12-13</th>\n",
       "      <td>10</td>\n",
       "      <td>8.5</td>\n",
       "      <td>439.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-12-20</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>223.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-12-27</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>149.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>111.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            quantity  revenue  country_enc\n",
       "week                                      \n",
       "2009-12-06       143    439.1          NaN\n",
       "2009-12-13        10      8.5        439.1\n",
       "2009-12-20         0      0.0        223.8\n",
       "2009-12-27         0      0.0        149.2\n",
       "2010-01-03         0      0.0        111.9"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now we drop the static variable\n",
    "\n",
    "X_train_enc = X_train_enc.drop(\"country\", axis=1)\n",
    "\n",
    "# Reset the index\n",
    "X_train_enc.set_index(\"week\", inplace=True)\n",
    "\n",
    "X_train_enc.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "2402ebb9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>quantity</th>\n",
       "      <th>revenue</th>\n",
       "      <th>country_enc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>week</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2011-07-03</th>\n",
       "      <td>103</td>\n",
       "      <td>163.90</td>\n",
       "      <td>511.378537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-07-10</th>\n",
       "      <td>666</td>\n",
       "      <td>1022.82</td>\n",
       "      <td>507.192048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-07-17</th>\n",
       "      <td>13</td>\n",
       "      <td>45.60</td>\n",
       "      <td>513.330476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-07-24</th>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>507.827765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-07-31</th>\n",
       "      <td>1000</td>\n",
       "      <td>1407.15</td>\n",
       "      <td>501.922791</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            quantity  revenue  country_enc\n",
       "week                                      \n",
       "2011-07-03       103   163.90   511.378537\n",
       "2011-07-10       666  1022.82   507.192048\n",
       "2011-07-17        13    45.60   513.330476\n",
       "2011-07-24         0     0.00   507.827765\n",
       "2011-07-31      1000  1407.15   501.922791"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now we repeat for the test set\n",
    "\n",
    "# Find the encoding values\n",
    "test_enc = (\n",
    "    X_test\n",
    "    .groupby(['country'])['revenue']\n",
    "    .expanding()\n",
    "    .mean()\n",
    "    .shift()\n",
    ").reset_index()\n",
    "\n",
    "test_enc.rename(columns = {\"revenue\": \"country_enc\"}, inplace = True)\n",
    "\n",
    "# join encoded variable\n",
    "X_test_enc = X_test.reset_index().merge(test_enc)\n",
    "\n",
    "# Drop original variable\n",
    "X_test_enc = X_test_enc.drop(\"country\", axis=1)\n",
    "\n",
    "# Reset the index\n",
    "X_test_enc.set_index(\"week\", inplace=True)\n",
    "\n",
    "# Remove data that belongs to the train set\n",
    "X_test_enc = X_test_enc[X_test_enc.index > pd.to_datetime('2011-06-30')]\n",
    "\n",
    "X_test_enc.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86a89e3e",
   "metadata": {},
   "source": [
    "That's it!\n",
    "\n",
    "As you can see, with this way of encoding the static feature, we need to do a lot of the work manually, and we need to be careful to have enough data in the train set, and to split the data correctly after the encoding."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77b803d1",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "fsml",
   "language": "python",
   "name": "fsml"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "173.267px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
